#!/bin/bash


# 判断是否为空置
if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d '-1 days' +%F`
fi


# SELECT 查询语句
DIM_ADS_PLATFORM_INFO_FULL_SQL="
USE jtp_ads_warehouse ;
WITH
    t1 AS (
        SELECT id, ad_id, platform_id, create_time, cancel_time
        FROM ods_ads_platform_full
        WHERE dt = '${data_date}'
    )
    , t2 AS (
        SELECT id, product_id, material_id, group_id AS ad_group_id, ad_name
        FROM ods_ads_info_full
        WHERE dt = '${data_date}'
    )
    , t3 AS (
        SELECT id, name AS product_name, price AS product_price
        FROM ods_product_info_full
        WHERE dt = '${data_date}'
    ),
    t4 AS (
        SELECT id, platform AS product_name, platform_alias_zh AS product_name_zh
        FROM ods_platform_info_full
        WHERE dt = '${data_date}'
    )
INSERT OVERWRITE TABLE dim_ads_platform_info_full PARTITION (dt = '${data_date}')
SELECT
    t1.id
    , t1.ad_id
    , t2.ad_name, t2.ad_group_id
    , t2.product_id
    , t3.product_name, t3.product_price
    , t2.material_id
    , t1.platform_id
    , t4.product_name, t4.product_name_zh
    , t1.create_time, t1.cancel_time
FROM t1
    LEFT JOIN t2 ON t1.ad_id = t2.id
    LEFT JOIN t3 ON t2.product_id = t3.id
    LEFT JOIN t4 ON t1.platform_id = t4.id
;
"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${DIM_ADS_PLATFORM_INFO_FULL_SQL}"

